package com.mx.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mx.dao.impl.ExportExcelDaoImpl;
import com.mx.entity.Person;
import com.mx.utils.DownloadUtils;
import com.mx.utils.ExportExcel;

@Transactional
@Service("ExportExcelService")
public class ExportExcelService {
	// 注入我们的daoimpl
	@Autowired
	private ExportExcelDaoImpl excelDaoImpl;

	public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
		// 导出文件
		// 声明一个工作薄
		// HSSFWorkbook wb = new HSSFWorkbook();
		XSSFWorkbook wb = new XSSFWorkbook();
		// 声明一个单子并命名
		XSSFSheet sheet = wb.createSheet("person");
		// 给单子名称一个长度
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式
		Map<String, CellStyle> style = new ExportExcel().createStyles(wb);
		CellRangeAddress cra = new CellRangeAddress(0, 1, (short) 0, (short) 2);
		sheet.addMergedRegion(cra);
		// 创建第一行（也可以称为表头）
		XSSFRow row1 = sheet.createRow(0);
		XSSFCell cell1 = row1.createCell((short) 0);
		cell1.setCellValue("用户信息");
		cell1.setCellStyle(style.get("header"));

		// 第二行
		XSSFRow row = sheet.createRow(2);
		// 给表头第一行一次创建单元格
		XSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("name");
		cell.setCellStyle(style.get("title"));
		cell = row.createCell((short) 1);
		cell.setCellValue("pwd");
		cell.setCellStyle(style.get("title"));
		cell = row.createCell((short) 2);
		cell.setCellValue("desca");
		cell.setCellStyle(style.get("title"));
		List<Person> list = excelDaoImpl.getExcelData();
		/*
		 * List<Person> list = new ArrayList<Person>(); list.add(new
		 * Person("zhangsan", "123456", "123654")); list.add(new Person("lisi",
		 * "567656", "656")); list.add(new Person("wangwu", "43434", "324234"));
		 */
		// 向单元格里填充数据
		for (short i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 3);
			XSSFCell celld0 = row.createCell(0);
			celld0.setCellValue(list.get(i).getName());
			celld0.setCellStyle(style.get("data2"));
			XSSFCell celld1 = row.createCell(1);
			celld1.setCellValue(list.get(i).getPwd());
			celld1.setCellStyle(style.get("data2"));
			XSSFCell celld2 = row.createCell(2);
			celld2.setCellValue(list.get(i).getDesca());
			celld2.setCellStyle(style.get("data2"));
		}
		// 默认导出到E盘下
		FileOutputStream out = new FileOutputStream("D://学生表.xlsx");
		wb.write(out);
		out.close();
		JOptionPane.showMessageDialog(null, "导出成功!");
		System.out.println("文件导出成功");
		// 返回文件路径
		String fileUrl = "D://学生表.xlsx";
		// 下载文件到用户
		File downloadFile = new File(fileUrl);
		DownloadUtils.download(request, response, downloadFile, "学生表.xlsx");

	}

	// 下载模版
	/**
	 * 下载模版
	 * @param modelname 模版名
	 * @throws IOException  异常
	 */
	public void downloadModelExcel(HttpServletRequest request, HttpServletResponse response,File downloadFile, String modelname) throws IOException {
         DownloadUtils.download(request, response, downloadFile, modelname);
	}
	//读取模版中的数据
	/**
	 * @param file 
	 * @throws InvalidFormatException 异常
	 * @throws IOException 异常
	 */
	@Test
	public  void readExcelToDateBase(File  file ) throws InvalidFormatException, IOException{
		//File  file=new File("D://serverlData//usermodel//upload//学生表.xlsx");
		XSSFWorkbook  wb=new XSSFWorkbook(file);
		//将excel读出来
		 int sheet_numbers = wb.getNumberOfSheets();//获取表的总数 
		 String[] sheetnames=new String[sheet_numbers];  
		 for (int i = 0; i < sheet_numbers; i++) {
			 XSSFSheet sheet = wb.getSheetAt(i);  //获取 某个表     
			 //输出sheet名字
             sheetnames[i] = sheet.getSheetName();//获取表名，存入数组 
             System.out.println("还行"+sheet.getLastRowNum());
             for( int rows=2;rows<=sheet.getLastRowNum();rows++){//有多少行   sheet.getLastRowNum()  第3行取我们的数据
            	 XSSFRow row = sheet.getRow(rows);//取得某一行   对象
            	 System.out.println(row.getCell(0).toString()+","+row.getCell(1).toString()+","+row.getCell(2).toString());
            	 Person  person=new Person(row.getCell(0).toString(),row.getCell(1).toString(),row.getCell(2).toString());
            	 excelDaoImpl.uploadUserInfoExcel(person);       	  
            	 }
             
             }
		 System.out.println("cg");
		wb.close();
		 
	}
}
